package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IExShiftService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;

@Service("ExShiftService")
public class ExShiftService implements IExShiftService {
    private Connection connPre = null;

    @Override
    public void syncRecord() throws Exception {
        final String recordName = "tbl_outJzTable";
        //final String recordName2 = "tbl_qxsjzShiftMiddleETC";
        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            String recordStr = DBUtils.getCurSynRecordNum(connPre, recordName);
            XxlJobLogger.log("[普通流水]本次最大记录RECORDID为:" + recordStr);

            String dbCurTime = DBUtils.getDBCurTimeBefore3Min(connPre);
            XxlJobLogger.log("[普通流水]本次同步截止时间为:" + dbCurTime);

            XxlJobLogger.log("=====开始执行同步MTC工班操作===");
            StringBuffer sql = new StringBuffer();
            sql.append("insert into TB_SYNC_EXSHIFT(SEQ_ID,LISTNO,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,TOTAL_COUNT,TOTAL_MONEY,CASE_MONEY,NONCASH_MONEY,PAYCARD_MONEY," +
                    " ETC_TOTALCOUNT,ETC_TOTALMONEY,SQUARD_TYPE,OPERATORNO,INSERT_TIME)" +
                    " select SEQ_SYNC_EXSHIFT_ID.nextval AS seq_id,LISTNO,StationNo,SquadDate,SquadNo,LaneNo,nvl(totalcount,0) totalcount,"+
                    " (CASE_MONEY + NONCASH_MONEY) totalmoney,CASE_MONEY,NONCASH_MONEY,PAYCARD_MONEY,"+
                    " nvl(etc_totalcount,0) etc_totalcount,etc_totalmoney,'mtc' AS SQUARD_TYPE,OPERATORNO,SYSDATE"+
                    " FROM ("+
                    "   SELECT LISTNO,StationNo,SquadDate,SquadNo,LaneNo,accountsCount as totalcount,"+
                    //"   (cpccashtoll + etctocashtoll + papercashtoll +nopasscashtoll) as CASE_MONEY,"+
                    "machinetoll as CASE_MONEY,"+
                    "   (cpcmobiletoll + papermobiletoll + etcmobiletoll +nopassnocashtoll) as NONCASH_MONEY,etcswiptoll as PAYCARD_MONEY,"+
                    "   exPassCount AS etc_totalcount,etcnormtoll AS etc_totalmoney,OPERATORNO,inserttabletime"+
                    "   from TBL_OutJzTable"+
                    " ) t"+
                    " where inserttabletime>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ");
            Integer count = DBUtils.execute(connPre, sql.toString());
            XxlJobLogger.log("[MTC出口工班流水]本次从汇聚系统共同步(条):" + count);

            /**-------------------------------------------------
            //同步ETC出口工班
            /*-------------------------------------------------*/
            XxlJobLogger.log("=====开始执行同步ETC出口工班操作===");
            StringBuffer sql2 = new StringBuffer();
            sql2.append("insert into TB_SYNC_EXSHIFT(SEQ_ID,LISTNO,STATION_ID,SQUAD_DATE,SQUAD_NO,LANE_NO,TOTAL_COUNT,TOTAL_MONEY," +
                    " CASE_MONEY,NONCASH_MONEY,PAYCARD_MONEY,ETC_TOTALCOUNT,ETC_TOTALMONEY,SQUARD_TYPE,OPERATORNO,INSERT_TIME)" +
                    " select SEQ_SYNC_EXSHIFT_ID.nextval,LISTNO,StationNo,SquadDate,SquadNo,LaneNo,totalcount,TRAFFICMNY,"+
                    "  NULL,NULL,NULL,NULL,NULL,'etc',OPERATORNO,SYSDATE"+
                    " from TBL_qxsjzShiftMiddleETC"+
                    " where laneNo >= 50 and inserttabletime>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') " +
                    "       and inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss')");
            Integer count2 = DBUtils.execute(connPre, sql2.toString());
            XxlJobLogger.log("[ETC出口工班流水]本次从汇聚系统共同步(条):" + count2);

            count = DBUtils.modifySynRecordNum(connPre, recordName, recordStr, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[异常][普通]更新记录时间异常");
                throw new Exception("[普通]更新记录时间异常");
            }
            XxlJobLogger.log("[普通]更新记录时间为:" + dbCurTime);

            connPre.commit();
            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}
